SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[GetBatchLot] ( @DrugId NVARCHAR(20) )
RETURNS NVARCHAR(200)
AS 
    BEGIN
        DECLARE @Return NVARCHAR(200);
        WITH    Data
                  AS ( SELECT   DrugId ,
                                ExpDate ,
                                BatchLot
                       FROM     dbo.Stock
                       WHERE    DrugId = @DrugId
                     )
            SELECT  @Return = STUFF(( SELECT    ', ' + BatchLot + ' - '
                                                + SUBSTRING(CONVERT(VARCHAR, ExpDate, 102),
                                                            0, 8)
                                      FROM      Data data1
                                      WHERE     data.DrugId = data1.DrugId
                                    FOR
                                      XML PATH('')
                                    ), 1, 1, '')
            FROM    Data data
        RETURN @Return
    END
        
GO
